<?php

function update_departemen(){
	$result = my_query( "SELECT * FROM jabatan ORDER BY urut") ;
	while ($row = my_fetch_array($result)) {
		$jabatan_id=$row['jabatan_id'] ;
		$nomor_posisi=$row['nomor_posisi'] ;
		$a_pos_id=array(0,0,0,0,0,0,0);
		if($nomor_posisi=='0000000'){
		}else{
			for($i=1; $i<= $row['level']; $i++){
				$ctemp=substr($row['nomor_posisi'],0,$i).str_repeat('0',8-$i) ;	
				$res_pos = my_query( 
					"SELECT jabatan_id FROM jabatan 
					WHERE nomor_posisi='".$ctemp."'  
					AND level=".$i) ;	
				if(my_num_rows($res_pos)>0){
					$poss=my_fetch_array($res_pos);
					$a_pos_id[$i-1]=$poss['jabatan_id'];
				}	
			}
		}	 
 		$datas = array(
			'pos_id_1'=>my_type_data_int( $a_pos_id[0] ), 
			'pos_id_2'=>my_type_data_int( $a_pos_id[1] ), 
			'pos_id_3'=>my_type_data_int( $a_pos_id[2] ), 
			'pos_id_4'=>my_type_data_int( $a_pos_id[3] ), 
			'pos_id_5'=>my_type_data_int( $a_pos_id[4] ), 
			'pos_id_6'=>my_type_data_int( $a_pos_id[5] ), 
			'pos_id_7'=>my_type_data_int( $a_pos_id[6] ), 
		); 	
		my_update_record('jabatan' ,'jabatan_id' , $jabatan_id , $datas);
	} 
	return true;
} 		

function update_realisasi(){
	$result = my_query( "SELECT * FROM a_realisasi_temp") ;
	while ($row = my_fetch_array($result)) {
		$karyawan_id=$row['karyawan_id'];
		
		$alpha='T';
		$catatan='';
		$jam_datang='00:00:00';
		$jam_pulang='00:00:00';

		$i = 1;
		while ($i <= 31) {
			$tgl=sprintf( '%02d',$i) ;
			$field_tgl='tgl_'.$tgl;
			$status_absen=$row[$field_tgl];
			$status_absens=my_get_data_by_id('a_status_absen','initial_absen',"'".$status_absen."'");
			$status_absen_id=$status_absens['status_absen_id'];
			$sc_status_absen_id=0;
			$sc_jadwal_kerja_kelompok_id=0;	
			//cek apakah a_realisasi sudah ada
			$query = "
				SELECT * FROM a_realisasi 
				WHERE karyawan_id =".$karyawan_id."  
				AND field_tgl='".$field_tgl."'" ; 
			$res = my_query($query);
			$rw = my_fetch_array($res);
			$adadata=false;
			if($rw){
				$realisasi_id=$rw['realisasi_id'];
				$alpha=$rw['alpha'];
				$catatan=$rw['catatan'];
				$jam_datang=$rw['jam_datang'];
				$jam_pulang=$rw['jam_pulang'];
				$sc_status_absen_id=$rw['sc_status_absen_id'];
				$sc_jadwal_kerja_kelompok_id=$rw['sc_jadwal_kerja_kelompok_id'];
				
				$adadata=true;
			}	
			$datas = array(
					'karyawan_id'=>my_type_data_int( $karyawan_id ), 
					'field_tgl'=>my_type_data_str( $field_tgl ), 
					'alpha'=>my_type_data_str( $alpha ), 
					'catatan'=>my_type_data_str( $catatan ), 
					'jam_datang'=>my_type_data_str( $jam_datang ), 
					'jam_pulang'=>my_type_data_str( $jam_pulang ), 
					'status_absen_id'=>my_type_data_int( $status_absen_id ), 
					'sc_status_absen_id'=>my_type_data_int( $sc_status_absen_id ), 
					'sc_jadwal_kerja_kelompok_id'=>my_type_data_int( $sc_jadwal_kerja_kelompok_id ), 
			); 
			if($adadata){
				my_update_record('a_realisasi' ,'realisasi_id' , $realisasi_id , $datas);
			}else{
				my_insert_record('a_realisasi' , $datas);	
			}	
			$i++;
		}
  } 
   return true;
} 		

function update_a_absen_payroll(){
	$p_id =get_periode_absen();
	$periode_id=get_last_periode_id($p_id) ;
	$last_periode_id=get_last_periode_id($periode_id) ;
	$periodes = my_get_data_by_id('pr_periode','periode_id',$periode_id);
	$bulan = (int) date('m' , strtotime($periodes['tanggal']));
	$tahun = $periodes['tahun'] ;
	$enddate_11_10=$tahun.'-'.sprintf( '%02d',$bulan) .'-10';
	if($bulan =='01' ){
		$bulan = '12';
		$tahun-- ;	
	}else{
		$bulan-- ;
	}		
	$startdate_11_10=$tahun.'-'.sprintf( '%02d',$bulan) .'-11';	
	$startdate_01_31=$tahun.'-'.sprintf( '%02d',$bulan) .'-01';	
	$tgl_akhir=date('d', strtotime(get_last_date_by_month($tahun,$bulan))) ;
	$enddate_01_31=$tahun.'-'.sprintf( '%02d',$bulan) .'-'.$tgl_akhir;
	
	$query = "DELETE FROM a_absen_payroll WHERE periode_id ={$periode_id} ";
	my_query($query);

	$query = "
		SELECT karyawan_id FROM karyawan a 
		WHERE a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable)
		AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian) 		
	";
	$result = my_query($query);
	while($ey = my_fetch_array($result)){
		$karyawan_id=$ey['karyawan_id'];
		//HITUNG LEMBUR 
		$query = "SELECT tanggal,sum(hourpay) as hourpay, sum(jumlah_jam) as jumlah_jam  
				FROM a_spl 
				WHERE karyawan_id=".$ey['karyawan_id']."  
				AND tanggal BETWEEN '".$startdate_01_31."' AND '".$enddate_11_10."'
				GROUP BY tanggal ORDER BY DAY(tanggal)
				" ;
		$res = my_query($query);
		$lembur_jam=$lembur_lebih4jam=0;
		$lembur_jumlah_jam=$hourpay=$hourpay_56=$nact=$nact1= 0;
		while($ey1 = my_fetch_array($res)){
			if($ey1['tanggal']<=$enddate_01_31){
				//tambahan u makan
				$lembur_jam=$ey1['jumlah_jam'] ;
				if( $lembur_jam  >= 16){
					$lembur_lebih4jam+=3 ;
				}elseif( $lembur_jam  >= 12){	
					$lembur_lebih4jam+=2 ;
				}elseif( $lembur_jam  >=4){	
					$lembur_lebih4jam+=1 ;
				}else{
				}
			}
			if($ey1['tanggal']>=$startdate_11_10){
				$lembur_jumlah_jam+=$ey1['jumlah_jam'];
				$hourpay+=$ey1['hourpay'];
				if($nact+$ey1['jumlah_jam']<=56){
					$nact+=$ey1['jumlah_jam'];
					$hourpay_56+=$ey1['hourpay'];
				}else{	
					$nact1+=$ey1['jumlah_jam'];
				}			
			}
		}

		if($nact1<>0 AND $nact <=56){
			if(($nact1+$nact) > 56){
				$hourpay_56+=((56-$nact)* 2 - 0.5 );
			}else{	
				$hourpay_56+=($nact1* 2 - 0.5 );
			}
		}
 		
		//HITUNG KEHADIRAN 	
		$potong_absen_jam=$potong_transport=0;
		$query = "SELECT * FROM a_realisasi_log 
				WHERE karyawan_id={$karyawan_id}  
				AND	periode_id={$periode_id}
			 ";	
		$res1 = my_query($query); 	
		$hari_kerja_11_10=$lembur_otomatis=0;
		$hari_kerja_1_31=0;
		$cito_11_10=$insentif_pot_hari_1_31=$insentif_pot_jam_1_31=$insentif_pot_menit_1_31=0;
		while($ey1 = my_fetch_array($res1)){
			$status_absens=my_get_data_by_id('a_status_absen','status_absen_id',$ey1['status_absen_id']) ;
			if((int) $ey1['status_absen_id']==16 ){  //nanti direvisi status absen + kolom potong absensi
				$potong_absen_jam+=8;
			}
			if($status_absens['potong_transport']=="Y"){
				$potong_transport++;
			}

			if($status_absens['hari_kerja']=="Y"){
				$hari_kerja_11_10++;
			}
			if($status_absens['lembur_otomatis']=="Y"){
				$lembur_otomatis++;
			}			
			//HITUNG KEHADIRAN 11-... (=BULAN SEBELUMNYA)UNTUK INSENTIF	
			if( (int) str_replace('tgl_','',$ey1['field_tgl'])  > 10){
				if($status_absens['potong_insentif']=="Y"){
					$insentif_pot_hari_1_31++;
				}		
				if($status_absens['hari_kerja']=="Y"){
					$hari_kerja_1_31++;
				}
			}
		}

		// HITUNG ABSEN
		$query = "
			SELECT * FROM a_surat_ijin	WHERE 
			tanggal BETWEEN '".$startdate_11_10."' AND '".$enddate_11_10."' 
			AND karyawan_id=".$karyawan_id." 
			AND rekomendasi='T'"
		;
		$res = my_query($query);
		while($ey = my_fetch_array($res)){
			if( $ey['full_day']=='Y'){
			}else{
				if($ey['jam_sampai']>$ey['jam_dari']){
					$jam_dari=$ey['jam_dari'];
					$jam_sampai=$ey['jam_sampai'];
				}else{
					$jam_dari=$ey['jam_sampai'];
					$jam_sampai=$ey['jam_dari'];
				}
				$jam_menit=strtotime('2001-01-01 '.time_min($jam_sampai,$jam_dari));
				$potong_absen_jam+=(int) date("H",$jam_menit );
				$potong_absen_jam+=(int) date("i",$jam_menit )/60;
			}
		}
		
		//HITUNG KEHADIRAN 1-10 UNTUK INSENTIF
		$query = "SELECT * FROM a_realisasi_log 
				WHERE karyawan_id=".$karyawan_id." 
				AND	periode_id=".($last_periode_id)." 
				AND field_tgl IN (
				'tgl_01', 
				'tgl_02', 
				'tgl_03', 
				'tgl_04', 
				'tgl_05', 
				'tgl_06', 
				'tgl_07', 
				'tgl_08', 
				'tgl_09', 
				'tgl_10' )"
			 ;	
		$res2 = my_query($query); 	
		while($ey2 = my_fetch_array($res2)){
			$status_absens=my_get_data_by_id('a_status_absen','status_absen_id',$ey2['status_absen_id']) ;
			if($status_absens['potong_insentif']=="Y"){
				$insentif_pot_hari_1_31++;
			}
			if($status_absens['hari_kerja']=="Y"){
				$hari_kerja_1_31++;
			}
		}	
		// HITUNG POTONGAN INSENTIF JAM & MENIT
		$query = "
			SELECT * FROM a_surat_ijin	WHERE MONTH(tanggal)='".$bulan."'
			AND YEAR(tanggal)='".$tahun."' AND karyawan_id=".$karyawan_id." 
			AND potong_insentif='Y'"
		;

		$res = my_query($query);
		while($ey = my_fetch_array($res)){
			if( $ey['full_day']=='Y'){
			}else{
				if($ey['jam_sampai']>$ey['jam_dari']){
					$jam_dari=$ey['jam_dari'];
					$jam_sampai=$ey['jam_sampai'];
				}else{
					$jam_dari=$ey['jam_sampai'];
					$jam_sampai=$ey['jam_dari'];
				}
				$jam_menit=strtotime('2001-01-01 '.time_min($jam_sampai,$jam_dari));
				$insentif_pot_jam_1_31+=(int) date("H",$jam_menit );
				$insentif_pot_menit_1_31+=(int) date("i",$jam_menit );
			}
		}
	
		// Hitung Work On Call
		$query = "SELECT dua_jam,empat_jam from woc_tarif a 
			INNER JOIN kekaryawanan b ON a.golongan_jabatan_id=b.golongan_jabatan_id 
			WHERE b.karyawan_id = {$karyawan_id }";	
		$tarifs = my_fetch_array(my_query($query));

		$query = "SELECT tanggal,jumlah_jam FROM work_on_call_detail a 
			INNER JOIN work_on_call b ON a.work_on_call_id=b.work_on_call_id 	
			WHERE a.karyawan_id={$karyawan_id} 
			AND b.tanggal BETWEEN '".$startdate_11_10."' AND '".$enddate_11_10."' 
			ORDER BY b.tanggal " ;
		$res = my_query($query);

		$tgl=false;
		$cito_jam=$cito_lebih4jam=0;
		while($ey = my_fetch_array($res)){

			if( (int) $ey['jumlah_jam']  < 4){
				$cito_11_10+=$ey['jumlah_jam']*$tarifs['dua_jam'] ;
			}else{
				$cito_11_10+=$ey['jumlah_jam']*$tarifs['empat_jam'] ;
			}
			//tambahan u makan
			if($ey['tanggal']==$tgl){
			}else{
				if( $cito_jam  >= 4){
					$cito_lebih4jam+=1 ;
				}			
				$tgl=$ey['tanggal'] ;
				$cito_jam=0;
			}
			$cito_jam+=$ey['jumlah_jam'];
		}
		if($tgl){ //data terakhir
				if( $cito_jam  >= 4){
					$cito_lebih4jam+=1 ;
				}			
		}		
		
		//UPDATE a_absen_payroll
		$hourpay_56=round($hourpay_56);
		$datas = array(
			'karyawan_id'=>my_type_data_int($karyawan_id), 
			'periode_id'=>my_type_data_int($periode_id), 
			'hari_kerja_11_10'=>my_type_data_int($hari_kerja_11_10), 
			'lembur_otomatis'=>my_type_data_int($lembur_otomatis), 
			'hourpay'=>my_type_data_str($hourpay), 
			'hourpay_56'=>my_type_data_str($hourpay_56), 
			'lembur_jumlah_jam'=>my_type_data_str($lembur_jumlah_jam), 
			'lembur_lebih4jam'=>my_type_data_int($lembur_lebih4jam), 
			'hari_kerja_1_31'=>my_type_data_int($hari_kerja_1_31), 
			'cito_11_10'=>my_type_data_int($cito_11_10), 
			'cito_lebih4jam'=>my_type_data_int($cito_lebih4jam), 
			'insentif_pot_hari_1_31'=>my_type_data_int($insentif_pot_hari_1_31), 
			'insentif_pot_jam_1_31'=>my_type_data_int($insentif_pot_jam_1_31), 
			'insentif_pot_menit_1_31'=>my_type_data_int($insentif_pot_menit_1_31), 
			'potong_absen_jam'=>my_type_data_str($potong_absen_jam), 
			'potong_transport'=>my_type_data_int($potong_transport), 
		);
		my_insert_record('a_absen_payroll' , $datas);						
	}	
   return true;
} 


function update_periode_spl(){
	$result = my_query( "SELECT * FROM a_spl") ;
	while ($row = my_fetch_array($result)) {
		if( $row['periode_id']==0 ){
			$periode_id=get_periode_id_by_tanggal($row['tanggal']) ;
			$datas = array(
				'periode_id'=>my_type_data_int( $periode_id )
			); 
			my_update_record('a_spl' ,'spl_id' , $row['spl_id'] , $datas);
		}
	} 
   return true;
} 		
